﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using CLEX.DataContracts;

namespace CLEX.Server.DAL
{
    public sealed class DatabaseDAL : IDatabaseDAL
    {
        private const string DATABASE_ID_COL = "database_id";
        private const string OWNER_USER_ID = "owner_user_id";
        private const string NAME_COL = "name";
        private const string CONNECTION_STRING_COL = "connection_string";

        private const string GET_ALL_SP = "database_get_all";
        private const string CREATE_SP = "database_create";
        private const string UPDATE_SP = "database_update";
        private const string DELETE_SP = "database_delete";

        private readonly string connectionString;

        public DatabaseDAL(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public List<DatabaseData> GetAll()
        {
            List<DatabaseData> result = new List<DatabaseData>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GET_ALL_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int databaseIdCol = reader.GetOrdinal(DATABASE_ID_COL);
                        int ownerUserIdCol = reader.GetOrdinal(OWNER_USER_ID);
                        int nameCol = reader.GetOrdinal(NAME_COL);
                        int connectionStringCol = reader.GetOrdinal(CONNECTION_STRING_COL);

                        while (reader.Read())
                        {
                            result.Add(new DatabaseData()
                            {
                                DatabaseId = reader.GetInt32(databaseIdCol),
                                OwnerUserId = reader.GetInt32(ownerUserIdCol),
                                Name = reader.GetString(nameCol),
                                ConnectionString = reader.GetString(connectionStringCol),
                            });
                        }
                    }
                }
            }

            return result;
        }

        public DatabaseData Get(int userId)
        {
            DatabaseData result;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GET_ALL_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int databaseIdCol = reader.GetOrdinal(DATABASE_ID_COL);
                        int ownerUserIdCol = reader.GetOrdinal(OWNER_USER_ID);
                        int nameCol = reader.GetOrdinal(NAME_COL);
                        int connectionStringCol = reader.GetOrdinal(CONNECTION_STRING_COL);

                        reader.Read();
                        result = new DatabaseData()
                        {
                            DatabaseId = reader.GetInt32(databaseIdCol),
                            OwnerUserId = reader.GetInt32(ownerUserIdCol),
                            Name = reader.GetString(nameCol),
                            ConnectionString = reader.GetString(connectionStringCol),
                        };
                    }
                }
            }

            return result;
        }

        public DatabaseData Create(DatabaseData database)
        {
            DatabaseData result = new DatabaseData();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(CREATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    var databaseId = cmd.CreateParameter();
                    databaseId.Direction = ParameterDirection.Output;
                    databaseId.ParameterName = DATABASE_ID_COL;
                    databaseId.Size = 4;
                    databaseId.DbType = DbType.Int32;
                    cmd.Parameters.Add(databaseId);
                    cmd.Parameters.AddWithValue(OWNER_USER_ID, database.OwnerUserId);
                    cmd.Parameters.AddWithValue(NAME_COL, database.Name);
                    cmd.Parameters.AddWithValue(CONNECTION_STRING_COL, database.ConnectionString);
                    cmd.ExecuteNonQuery();

                    result.DatabaseId = (int)databaseId.Value;
                }
            }

            result.OwnerUserId = database.OwnerUserId;
            result.Name = database.Name;
            result.ConnectionString = database.ConnectionString;
            return result;
        }

        public void Update(DatabaseData database)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(UPDATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(DATABASE_ID_COL, database.DatabaseId);
                    cmd.Parameters.AddWithValue(OWNER_USER_ID, database.OwnerUserId);
                    cmd.Parameters.AddWithValue(NAME_COL, database.Name);
                    cmd.Parameters.AddWithValue(CONNECTION_STRING_COL, database.ConnectionString);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void Delete(DatabaseData database)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(DELETE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(DATABASE_ID_COL, database.DatabaseId);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
